package mbms.mobilebudget.database;

import java.sql.SQLException;

import mbms.mobilebudget.entity.BankAccount;
import mbms.mobilebudget.entity.CreditCard;
import mbms.mobilebudget.entity.Exchange;
import mbms.mobilebudget.entity.Expense;
import mbms.mobilebudget.entity.Income;
import mbms.mobilebudget.entity.User;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.RuntimeExceptionDao;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;

public class DatabaseHandler extends OrmLiteSqliteOpenHelper{

	public static final String DATABASE_NAME = "mbms.db";
	
	public static final int DATABASE_VERSION=20;
	
	private Dao<Expense, Integer> expenseDao;
	private Dao<Income, Integer> incomeDao;
	private Dao<User, Integer> userDao;
	private Dao<CreditCard, Integer> creditCardDao;
	private Dao<BankAccount, Integer> bankAccountDao;
	private Dao<Exchange, Integer> exchangeDao;
	
	private RuntimeExceptionDao<Expense, Integer> expenseRuntimeDao = null;
	private RuntimeExceptionDao<Income, Integer> incomeRuntimeDao = null;
	private RuntimeExceptionDao<User, Integer> userRuntimeDao = null;
	private RuntimeExceptionDao<CreditCard, Integer> creditCardRuntimeDao = null;
	private RuntimeExceptionDao<BankAccount, Integer> bankAccountRuntimeDao = null;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource) {
		try {
			TableUtils.createTable(connectionSource, User.class);
			TableUtils.createTable(connectionSource, BankAccount.class);
			TableUtils.createTable(connectionSource, CreditCard.class);
			TableUtils.createTable(connectionSource, Expense.class);
			TableUtils.createTable(connectionSource, Income.class);
			TableUtils.createTable(connectionSource, Exchange.class);
			//getExchangeDao().create(new Exchange(null, 85.00, 1.88, 2.45));
		
			Log.d(DATABASE_NAME, "Database created");
		} catch (SQLException e) {
			Log.e(DatabaseHandler.class.getName(), "Unable to create databases", e);
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase sqliteDatabase, ConnectionSource connectionSource, int oldVer,
			int newVer) {
		try {
			TableUtils.dropTable(connectionSource, Income.class, true);
			TableUtils.dropTable(connectionSource, Expense.class, true);
			TableUtils.dropTable(connectionSource, CreditCard.class, true);
			TableUtils.dropTable(connectionSource, BankAccount.class, true);
			TableUtils.dropTable(connectionSource, User.class, true);
			TableUtils.dropTable(connectionSource, Exchange.class, true);
			onCreate(sqliteDatabase, connectionSource);
			Log.d(DATABASE_NAME, "Database upgraded");
		} catch (SQLException e) {
			Log.e(DatabaseHandler.class.getName(), "Unable to upgrade database from version " + oldVer + " to new "
					+ newVer, e);
		}
		
	}
	public Dao<User, Integer> getUserDao() throws SQLException {
		if (userDao == null) {
			userDao = getDao(User.class);
		}
		return userDao;
	}
	public Dao<CreditCard, Integer> getCreditCardDao() throws SQLException {
		if (creditCardDao == null) {
			creditCardDao = getDao(CreditCard.class);
		}
		return creditCardDao;
	}
	public Dao<BankAccount, Integer> getBankAccountDao() throws SQLException {
		if (bankAccountDao == null) {
			bankAccountDao = getDao(BankAccount.class);
		}
		return bankAccountDao;
	}
	public Dao<Expense, Integer> getExpenseDao() throws SQLException {
		if (expenseDao == null) {
			expenseDao = getDao(Expense.class);
		}
		return expenseDao;
	}
	public Dao<Income, Integer> getIncomeDao() throws SQLException {
		if (incomeDao == null) {
			incomeDao = getDao(Income.class);
		}
		return incomeDao;
	}
	public Dao<Exchange, Integer> getExchangeDao() throws SQLException {
		if (exchangeDao == null) {
			exchangeDao = getDao(Exchange.class);
		}
		return exchangeDao;
	}
	@Override
	public void close() {
		super.close();
		userRuntimeDao = null;
		expenseRuntimeDao = null;
		incomeRuntimeDao = null ;
		bankAccountRuntimeDao = null ; 
		creditCardRuntimeDao = null ; 
	}

	public RuntimeExceptionDao<Expense, Integer> getExpenseRuntimeDao() {
		if(expenseRuntimeDao ==null){
			expenseRuntimeDao = getRuntimeExceptionDao(Expense.class);
		}
		return expenseRuntimeDao;
	}

	public RuntimeExceptionDao<Income, Integer> getIncomeRuntimeDao() {
		if(incomeRuntimeDao ==null){
			incomeRuntimeDao = getRuntimeExceptionDao(Income.class);
		}
		return incomeRuntimeDao;
	}

	public RuntimeExceptionDao<User, Integer> getUserRuntimeDao() {
		if(userRuntimeDao ==null){
			userRuntimeDao = getRuntimeExceptionDao(User.class);
		}
		return userRuntimeDao;
	}

	public RuntimeExceptionDao<CreditCard, Integer> getCreditCardRuntimeDao() {
		if(creditCardRuntimeDao ==null){
			creditCardRuntimeDao = getRuntimeExceptionDao(CreditCard.class);
		}
		return creditCardRuntimeDao;
	}

	public RuntimeExceptionDao<BankAccount, Integer> getBankAccountRuntimeDao() {
		if(bankAccountRuntimeDao ==null){
			bankAccountRuntimeDao = getRuntimeExceptionDao(BankAccount.class);
		}
		return bankAccountRuntimeDao;
	}



		

}



/*public static final String INCOME_TABLE_NAME= "income";
public static final String EXPENSE_TABLE_NAME= "expense";
public static final String USER_TABLE_NAME= "user";
public static final String BANK_ACCOUNT_TABLE_NAME= "bank_account";
public static final String CREDIT_CARD_TABLE_NAME= "credit_card";




public static final String INCOME_CURRENCY_TYPE= "currency";
public static final String INCOME_AMOUNT= "amount";
public static final String INCOME_INCOME_CATEGORY= "category";
public static final String INCOME_DATE= "date";
public static final String INCOME_IS_RECURRENT= "is_recurrent";
public static final String INCOME_RECURRENCE_PERIOD= "recurrence_period";
public static final String INCOME_EXPLANATION= "explanation";


public static final String EXPENSE_CURRENCY_TYPE= "currency";
public static final String EXPENSE_AMOUNT= "amount";
public static final String EXPENSE_EXPENSE_CATEGORY= "category";
public static final String EXPENSE_DATE= "date";
public static final String EXPENSE_IS_RECURRENT= "is_recurrent";
public static final String EXPENSE_RECURRENCE_PERIOD= "recurrence_period";
public static final String EXPENSE_EXPLANATION= "explanation";

public static final String BANK_ACCOUNT_BANK_NAME= "bank_name";
public static final String BANK_ACCOUNT_BRANCH= "branch";
public static final String BANK_ACCOUNT_ACCOUNT_NUMBER = "account_number";
public static final String BANK_ACCOUNT_IBAN = "iban";
public static final String BANK_ACCOUNT_ACCOUNT_TYPE= "account_type";
public static final String BANK_ACCOUNT_CURRENCY_TYPE= "currency";

public static final String CREDIT_CARD_CREDIT_CARD_NAME= "card_name";
public static final String CREDIT_CARD_BANK_NAME= "bank_name";
public static final String CREDIT_CARD_LAST_FOUR_DIGIT= "last_four_digit";
public static final String CREDIT_CARD_CURRENCY_TYPE= "currency";
public static final String CREDIT_CARD_BRANCH_NAME= "branch";

public static final String USER_FIRSTNAME= "firstname";
public static final String USER_LASTNAME= "lastname";
public static final String USER_USERNAME= "username";
public static final String USER_PASSWORD= "password";
public static final String USER_COMM_EMAIL= "comm_email";
public static final String USER_STATEMENT_EMAIL= "statement_email";
public static final String USER_SEC_QUESTION= "security_question";
public static final String USER_SEC_ANSWER= "security_answer";


public static final String ID= "id";
public static final String USER_ID= "user_id";
public static final String BANK_ACCOUNT_ID= "bank_account_id";
public static final String CREDIT_CARD_ID= "credit_card_id";

public DatabaseHandler(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);
}




@Override
public void onCreate(SQLiteDatabase db) {
	
	String sql =	"CREATE TABLE IF NOT EXISTS "+ INCOME_TABLE_NAME 
			+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ INCOME_CURRENCY_TYPE		+ " TEXT, " 
			+ INCOME_AMOUNT				+ " TEXT NOT NULL, "
			+ INCOME_INCOME_CATEGORY	+ " TEXT NOT NULL, "
			+ INCOME_DATE				+ " TEXT NOT NULL, "
			+ INCOME_IS_RECURRENT		+ " INTEGER NOT NULL, "
			+ INCOME_RECURRENCE_PERIOD	+ " TEXT,  "
			+ INCOME_EXPLANATION	+ " TEXT, "
			+ CREDIT_CARD_ID			+ " INTEGER , "
			+ BANK_ACCOUNT_ID			+ " INTEGER , "
			+ "FOREIGN KEY("+ CREDIT_CARD_ID+ ") REFERENCES "+CREDIT_CARD_TABLE_NAME+ " (id), "
			+ "FOREIGN KEY("+ BANK_ACCOUNT_ID+ ") REFERENCES "+BANK_ACCOUNT_TABLE_NAME+ " (id) )";	
	db.execSQL(sql);
	
	sql =	"CREATE TABLE IF NOT EXISTS "+ EXPENSE_TABLE_NAME 
			+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ EXPENSE_CURRENCY_TYPE		+ " TEXT, " 
			+ EXPENSE_AMOUNT			+ " TEXT NOT NULL, "
			+ EXPENSE_EXPENSE_CATEGORY	+ " TEXT NOT NULL, "
			+ EXPENSE_DATE				+ " TEXT NOT NULL, "
			+ EXPENSE_IS_RECURRENT		+ " INTEGER NOT NULL"
			+ EXPENSE_RECURRENCE_PERIOD	+ " TEXT, "
			+ EXPENSE_EXPLANATION		+ " TEXT, "
			+ CREDIT_CARD_ID			+ " INTEGER , "
			+ BANK_ACCOUNT_ID			+ " INTEGER , "
			+ "FOREIGN KEY("+ CREDIT_CARD_ID+ ") REFERENCES "+CREDIT_CARD_TABLE_NAME+ " (id), "
			+ "FOREIGN KEY("+ BANK_ACCOUNT_ID+ ") REFERENCES "+BANK_ACCOUNT_TABLE_NAME+ " (id) )";	
	db.execSQL(sql);
	
	sql =	"CREATE TABLE IF NOT EXISTS "+ BANK_ACCOUNT_TABLE_NAME 
			+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ BANK_ACCOUNT_BANK_NAME		+ " TEXT NOT NULL, " 
			+ BANK_ACCOUNT_BRANCH			+ " TEXT, "
			+ BANK_ACCOUNT_ACCOUNT_NUMBER	+ " TEXT NOT NULL, "
			+ BANK_ACCOUNT_IBAN				+ " TEXT NOT NULL, "
			+ BANK_ACCOUNT_ACCOUNT_TYPE		+ " TEXT NOT NULL, "
			+ BANK_ACCOUNT_CURRENCY_TYPE	+ " TEXT NOT NULL, " 
			+ USER_ID						+ " INTEGER , "
			+ "FOREIGN KEY("+ USER_ID+ ") REFERENCES "+USER_TABLE_NAME+ " (id) )";	
	db.execSQL(sql);
	
	sql =	"CREATE TABLE IF NOT EXISTS "+ CREDIT_CARD_TABLE_NAME 
			+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ CREDIT_CARD_BANK_NAME			+ " TEXT NOT NULL, "
			+ CREDIT_CARD_BRANCH_NAME		+ " TEXT, "
			+ CREDIT_CARD_LAST_FOUR_DIGIT	+ " TEXT NOT NULL, "
			+ CREDIT_CARD_CURRENCY_TYPE		+ " TEXT NOT NULL, "
			+ USER_ID						+ " INTEGER , "
			+ "FOREIGN KEY("+ USER_ID+ ") REFERENCES "+USER_TABLE_NAME+ " (id) )";	
	db.execSQL(sql);
	
	sql =	"CREATE TABLE IF NOT EXISTS "+ USER_TABLE_NAME 
			+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ USER_FIRSTNAME		+ " TEXT NOT NULL, "
			+ USER_LASTNAME			+ " TEXT NOT NULL, "
			+ USER_USERNAME			+ " TEXT UNIQUE NOT NULL , "
			+ USER_PASSWORD			+ " TEXT NOT NULL, "
			+ USER_COMM_EMAIL		+ " TEXT NOT NULL, "
			+ USER_STATEMENT_EMAIL	+ " TEXT, "
			+ USER_SEC_QUESTION		+ " TEXT NOT NULL, "
			+ USER_SEC_ANSWER		+ " TEXT NOT NULL)";	
	db.execSQL(sql);
	
	
}

@Override
public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
	  // Drop older table if existed
    db.execSQL("DROP TABLE IF EXISTS " + INCOME_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + CREDIT_CARD_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + BANK_ACCOUNT_TABLE_NAME);
    db.execSQL("DROP TABLE IF EXISTS " + EXPENSE_TABLE_NAME);
    Log.d("Drop", "Tables dropped");
    onCreate(db);
	
}
public User registerUser(User user, String message){
	SQLiteDatabase db2 = this.getWritableDatabase();
	ContentValues values = new ContentValues();

      values.put(USER_FIRSTNAME, user.getFirstName());
      values.put(USER_LASTNAME, user.getLastName());
      values.put(USER_USERNAME, user.getUsername());
      values.put(USER_PASSWORD,user.getPassword());
      values.put(USER_COMM_EMAIL, user.getCommEmailAddress());
      values.put(USER_STATEMENT_EMAIL, user.getStatementEmailAddress());
      values.put(USER_SEC_QUESTION, user.getSequrityQuestion());
      values.put(USER_SEC_ANSWER, user.getSequrityQuestionAnswer());
      
      try {
    	  db2.insert(USER_TABLE_NAME, null, values);
	      db2.close(); // Closing database connection
	      Log.d("Registration", user.getUsername()+ " successful");
		return user;
	} catch(SQLiteConstraintException a){
		message = user.getUsername()+ " is not unique";
		Log.d("Registration", message );
		return null;
	}
      catch (Exception e) {
    	message = user.getUsername()+ " failed";
		Log.d("Registration", user.getUsername()+ " failed");
		return null;
	}
     
}
/*public List<Income> listIncomes(Income income){
	 SQLiteDatabase db = this.getReadableDatabase();
	 if(income ==null){
		 return null;
	 }
	 Integer id =  income.getId();
	 String selectQuery = "SELECT I.id,I."+INCOME_CURRENCY_TYPE+","
			 + "I."+INCOME_AMOUNT+","
			 + "I."+INCOME_INCOME_CATEGORY+","
			 + "I."+INCOME_DATE+","
			 + "I."+INCOME_IS_RECURRENT+","
			 + "I."+INCOME_RECURRENCE_PERIOD+","
			 + "I."+INCOME_EXPLANATION+","
			 + "I."+CREDIT_CARD_ID+","
			 + "I."+BANK_ACCOUNT_ID
			 + " FROM " + INCOME_TABLE_NAME+ " I,  WHERE I."+USER_USERNAME+ " ='"+username+"' and "
		//	 + " U."+ USER_PASSWORD+" ='"+ password+ "'";
	 Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor!=null && cursor.moveToFirst()) {
			 User contact = new User(Integer.parseInt(cursor.getString(0)), 
		        	cursor.getString(1),cursor.getString(2),cursor.getString(3),
		        	cursor.getString(4),cursor.getString(5),cursor.getString(6),
		             cursor.getString(7), cursor.getString(8));
			 return contact;
		}
		else{
			return null;
		}

	return null;
	
}
public Income addIncome(Income income, String message){
	SQLiteDatabase db2 = this.getWritableDatabase();
	ContentValues values = new ContentValues();

      values.put(INCOME_AMOUNT,income.getAmount());
      values.put(INCOME_CURRENCY_TYPE, income.getCurrencyType().toString());
      values.put(INCOME_DATE, income.getDateOfIncome().toString());
      values.put(INCOME_EXPLANATION, income.getIncomeExplanation().toString());
 /*   if(income.getIsRecurrent()){
    	  values.put(INCOME_IS_RECURRENT, 1);
      }
      else{
    	  values.put(INCOME_IS_RECURRENT, 0);
      }
      
      values.put(INCOME_INCOME_CATEGORY, income.getIncomeCategory().toString());
      
      try {
    	  db2.insert(INCOME_TABLE_NAME, null, values);
	      db2.close(); // Closing database connection
	      Log.d("Income", income.getId()+ " successful");
		return income;
	} catch(SQLiteConstraintException a){
		message = income.getId()+ " constraint exception";
		Log.d("Income",  "failed");
		return null;
	}
      catch (Exception e) {
    	message = "Income "+income.getId()+ " failed";
		Log.d("Income",  "failed");
		return null;
	}
	return income;
}
public User loginUser(User user){
	 SQLiteDatabase db = this.getReadableDatabase();
	 if(user ==null){
		 return null;
	 }
	 String username= user.getUsername();
	 String password= user.getPassword();
	 String selectQuery = "SELECT  * FROM " + USER_TABLE_NAME 
			 + " U WHERE U."+USER_USERNAME+ " ='"+username+"' and "
			 + " U."+ USER_PASSWORD+" ='"+ password+ "'";
	 Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor!=null && cursor.moveToFirst()) {
			 User contact = new User(Integer.parseInt(cursor.getString(0)), 
		        	cursor.getString(1),cursor.getString(2),cursor.getString(3),
		        	cursor.getString(4),cursor.getString(5),cursor.getString(6),
		             cursor.getString(7), cursor.getString(8));
			 return contact;
		}
		else{
			return null;
		}

}


public List<User> testUser(){
	
	
	
	
	List<User> contactList = new ArrayList<User>();
	String selectQuery = "SELECT  * FROM " + USER_TABLE_NAME;
	SQLiteDatabase db = this.getReadableDatabase();
	Cursor cursor = db.rawQuery(selectQuery, null);
	if (cursor.moveToFirst()) {
        do {
        	User contact = new User();
            contact.setId(Integer.parseInt(cursor.getString(0)));
            contact.setFirstName(cursor.getString(1));
            contact.setLastName(cursor.getString(2));
            contact.setUsername(cursor.getString(3));
            contact.setPassword(cursor.getString(4));
            contact.setCommEmailAddress(cursor.getString(5));
            contact.setStatementEmailAddress(cursor.getString(6));
            contact.setSequrityQuestion(cursor.getString(7));
            contact.setSequrityQuestionAnswer(cursor.getString(8));
            // Adding contact to list
            contactList.add(contact);
        } while (cursor.moveToNext());
    }

	return contactList;
	 
}



*/